/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package br.ufpr.tads.suck.model;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author G0006185
 */
public class PlanoDao {

    private final Connection connection;
    private final String sqlinsert = "insert into plano (nome,cnpj,endereco,contato,telefone,email)values(?,?,?,?,?,?)";
    private final String sqlendereco = "insert into endereco (logradouro,numero,bairro,cidade,estado) values(?,?,?,?,?)";
    private final String sqlList = "select * from plano p,endereco e where p.endereco=e.id;";
    private final String sqlListones = "select * from plano p,endereco e where p.endereco=e.id and p.cnpj=?";
    private final String sqlup = "update plano set nome=?,cnpj=?,contato=?,telefone=?,email=? where id=?";
    private final String sqlupend = "update endereco set logradouro=?,numero=?,estado=?,bairro=?,cidade=? where id=?";
    private final String sqlListone = "select * from plano p,endereco e where p.endereco=e.id and p.id=?";
    private final String sqldel = "delete  from plano where id=?";




    public PlanoDao() throws IOException, FileNotFoundException, ClassNotFoundException, SQLException {
        this.connection = ConnectionFactory.getConnection();
    }

    public void addPlano(Plano plano) throws SQLException, NoSuchAlgorithmException {
        connection.setAutoCommit(false);
        PreparedStatement stmt = connection.prepareStatement(sqlinsert, PreparedStatement.RETURN_GENERATED_KEYS);
        stmt.setString(1, plano.getNome());
        stmt.setString(2, plano.getCnpj());
        stmt.setInt(3, this.gravaEndereco(plano));
        stmt.setString(4, plano.getContato());
        stmt.setString(5, plano.getTelefone());
        stmt.setString(6, plano.getEmail());
        stmt.executeUpdate();
        connection.commit();
        stmt.close();
        throw new AvisoException("Dados Inseridos com sucesso!");
    }
       public List<Plano> listPlano() throws SQLException{
          PreparedStatement stmt = connection.prepareStatement(sqlList);
         ResultSet rs = stmt.executeQuery();
         List<Plano> listPlano = new ArrayList();
         
           while(rs.next()){
               Plano plano = new Plano();
               Endereco endereco = new Endereco();
               plano.setNome(rs.getString("nome"));
               plano.setEmail(rs.getString("email"));
               plano.setTelefone(rs.getString("telefone"));
               plano.setCnpj(rs.getString("cnpj"));
               plano.setId(rs.getInt("id"));
               plano.setContato(rs.getString("contato"));
               
               endereco.setBairro(rs.getString("bairro"));
               endereco.setCidade(rs.getString("cidade"));
               endereco.setEstado(rs.getString("estado"));
               endereco.setLogradouro(rs.getString("logradouro"));
               endereco.setNumero(rs.getInt("numero"));
               plano.setEndereco(endereco);
               listPlano.add(plano);  
           }
         return listPlano;
       }  
        public List<Plano> listPlano(String cnpj) throws SQLException{
          PreparedStatement stmt = connection.prepareStatement(sqlListones);
         stmt.setString(1, cnpj);
         ResultSet rs = stmt.executeQuery();
         List<Plano> listPlano = new ArrayList();
         
           while(rs.next()){
               Plano plano = new Plano();
               Endereco endereco = new Endereco();
               plano.setNome(rs.getString("nome"));
               plano.setEmail(rs.getString("email"));
               plano.setTelefone(rs.getString("telefone"));
               plano.setCnpj(rs.getString("cnpj"));
               plano.setId(rs.getInt("id"));
               plano.setContato(rs.getString("contato"));
               
               endereco.setBairro(rs.getString("bairro"));
               endereco.setCidade(rs.getString("cidade"));
               endereco.setEstado(rs.getString("estado"));
               endereco.setLogradouro(rs.getString("logradouro"));
               endereco.setNumero(rs.getInt("numero"));
               plano.setEndereco(endereco);
               listPlano.add(plano);  
           }
         return listPlano;
       }
    public Plano listPlano(int id) throws SQLException{
        PreparedStatement stmt = connection.prepareStatement(sqlListone);
        stmt.setInt(1, id);
        System.out.println(id);
        ResultSet rs = stmt.executeQuery();
        rs.next();

        Plano plano = new Plano();
        Endereco endereco = new Endereco();
        plano.setNome(rs.getString("nome"));
        plano.setEmail(rs.getString("email"));
        plano.setTelefone(rs.getString("telefone"));
        plano.setCnpj(rs.getString("cnpj"));
        plano.setId(rs.getInt("id"));
        plano.setContato(rs.getString("contato"));

        endereco.setBairro(rs.getString("bairro"));
        endereco.setCidade(rs.getString("cidade"));
        endereco.setEstado(rs.getString("estado"));
        endereco.setLogradouro(rs.getString("logradouro"));
        endereco.setNumero(rs.getInt("numero"));
        plano.setEndereco(endereco);

        return plano;
       }
    public void upPlano(Plano plano) throws SQLException, NoSuchAlgorithmException{
         connection.setAutoCommit(false);
         PreparedStatement stmt = connection.prepareStatement(sqlup,PreparedStatement.RETURN_GENERATED_KEYS);
         stmt.setString(1, plano.getNome());
         this.upEndereco(plano);
         stmt.setString(2, plano.getCnpj());
         stmt.setString(3, plano.getContato());
         stmt.setString(4, plano.getTelefone());  
         stmt.setString(5, plano.getEmail());
         stmt.setInt(6, plano.getId()); 
         stmt.executeUpdate();
         connection.commit();
         stmt.close();
        throw new AvisoException("Dados Atualizados com sucesso!");
    }
    
     private int gravaEndereco(Plano plano) throws SQLException{
       PreparedStatement stm = connection.prepareStatement(sqlendereco,PreparedStatement.RETURN_GENERATED_KEYS);
       stm.setString(1, plano.getEndereco().getLogradouro());
       stm.setInt(2, plano.getEndereco().getNumero());
       stm.setString(3, plano.getEndereco().getBairro());
       stm.setString(4, plano.getEndereco().getCidade());
       stm.setString(5, plano.getEndereco().getEstado());
       
       stm.execute();
       return this.lerId(stm);    
    }
     private void upEndereco(Plano plano) throws SQLException{
         
       PreparedStatement stm = connection.prepareStatement(sqlupend,PreparedStatement.RETURN_GENERATED_KEYS);
       stm.setString(1, plano.getEndereco().getLogradouro());
       stm.setInt(2, plano.getEndereco().getNumero());
       stm.setString(3, plano.getEndereco().getEstado());
       stm.setString(4, plano.getEndereco().getBairro());
       stm.setString(5, plano.getEndereco().getCidade());
        stm.setInt(6, plano.getId());
       
       
       stm.execute();
          
    }
     
       private int lerId(PreparedStatement stmt) throws SQLException {
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        return rs.getInt(1);
    } 
       public void delPlano(int id) throws SQLException{
        connection.setAutoCommit(false);
        PreparedStatement stmt = connection.prepareStatement(sqldel);
        int end = listPlano(id).getId();
        stmt.setInt(1, id);
        stmt.execute();
        delEndereco(end);
        connection.commit();
        stmt.close();
        throw new AvisoException("Atendente Excluido com Sucesso!");
    }
    
    private void delEndereco(int id) throws SQLException{
        
         PreparedStatement stmt = connection.prepareStatement("delete from endereco where id=?");
         stmt.setInt(1, id);
        stmt.execute();
        stmt.close();
    }

}
